IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetListAuthenStaff')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetListAuthenStaff
GO

/****** Object:  StoredProcedure [dbo].[USP_GetListAuthenStaff]    Script Date: 10/27/2010 01:01:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 31/10/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetListAuthenStaff]
	-- Add the parameters for the stored procedure here
	@staff_code int,
	@current_staff_code int,
	@department_code int,
	@group_code int,
	@auth_staff_status int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

    -- Modified by Boonyarit Yingvoragan
    -- Modified date 22/11/2553
	declare @table as table(department_code int)
	declare @level as int
	
	-- if you want to select all department, use the staff code's department.
	if @department_code = 0
		select @department_code = department_code from staff where staff_code = @current_staff_code

	-- find out the department level of staff
	select @level = mas.master_department_level 
	from stp_department dep 
	inner join master_department mas on dep.master_department_code = mas.master_department_code
	inner join staff s on s.department_code = dep.department_code 
	where s.staff_code = @staff_code 
	
	-- if you are super admin, you can see every department.
	if @department_code = 0
	begin
		insert @table 
		select dep.department_code
		from stp_department dep 
		where department_code > 0
	end
	
	-- you are normal staff, you can see only your child department.
	if @department_code > 0
	begin
		insert @table select @department_code
		while @level < 10
		begin
			insert @table 
			select department_code 
			from stp_department 
			where department_parent in (select department_code from @table) 
			set @level = @level + 1
		end
	end

	-- =============================================
	-- Query data from authenticated group
	-- =============================================
	select	
			SeqNo = ROW_NUMBER() OVER (ORDER BY dept.department_code) ,
			auth_staff.staff_code staff_code,
			s.staff_firstname,
			s.staff_lastname,
			auth_group.group_code group_user_code,
			g.group_user_name,
			dept_hirachy = dept.department_name ,--(select dbo.UFN_GetDepartmentAsHirachyByDepartmentCode(dept.department_code)),
			dept.department_code,
			dept.department_name,
			auth_staff.auth_staff_status,
			dept.department_parent,
			mas.master_department_level 
	from	stp_auth_department as auth
	join	stp_department as dept on auth.department_code = dept.department_code
	join	master_department as mas on mas.master_department_code = dept.master_department_code 
	join	stp_auth_group auth_group on auth_group.auth_dep_code = auth.auth_department_code 
	join	group_user g on g.group_user_code = auth_group.group_code 
	join	stp_auth_staff auth_staff on auth_staff.auth_dep_code = auth_group.auth_group_code 
	join	staff s on s.staff_code = auth_staff.staff_code 
	where	auth.department_code in  (select department_code from @table where department_code is not null) and
			@group_code in (0, auth_group.group_code ) and
			@staff_code in (0, auth_staff.staff_code) and
			@auth_staff_status in (0, auth_staff.auth_staff_status )
	group by auth_staff.staff_code, s.staff_firstname, s.staff_lastname, 
				auth_group.group_code, g.group_user_name, dept.department_code , 
				dept.department_name ,auth_staff.auth_staff_status,dept.department_parent, 
				mas.master_department_level
	order by mas.master_department_level

END

GO
